A New Basis for Spreadsheet Computing: Interval Solver for Microsoft Excel

There is a fundamental mismatch between the computational basis of spreadsheets and our knowledge of the real world. In spreadsheets numerical data is represented as exact numbers and their mutual relations as functions, whose values (output) are computed from given argument values (input). However, in the real world data is often inexact and uncertain in many ways and the relationships, i.e., constraints, between input and output are far more complicated. This paper shows that Interval Constraint Solving, an emerging Artificial Intelligence based technology, provides a more versatile and useful foundation for spreadsheets. The new computational basis is 100% downward compatible with the traditional spreadsheet paradigm. The idea has been successfully integrated with Microsoft Excel as the add-in Interval Solver that seamlessly upgrades the arithmetic core of Excel into interval constraint solving. The product has been downloaded by thousands of end-users in about 70 countries around the world and has been used in various applications on business computing, engineering, education and science. There is an intriguing chance for a major breakthrough of the AI technology on the spreadsheet platform: Tens of millions of Excel users are making important decisions based on spreadsheet calculations. 1. Real world spreadsheet computing The world is full of uncertainty and complexity. Everyday we are faced with questions like: How can I live within the given budget? Is this technical design possible, given the inaccurate component data? Uncertain data and constraints are extensively used in decision making. But spreadsheets, one of the most commonly used decision making aid of today, force us to use exact numbers for representing inexact data, thus distorting reality. For example, consider the problem of computing the present value p of a future cash flow c that will be received after three years. If the annual future interest rates are r1, r2, and r3 then p can be computed by using the (discounting) formula: /100)) r (1 /100) (1 /100) ((1 c p 3 2 1 + ⋅ + ⋅ + = (1.1) Copyright © 1999, American Association for Artificial Intelligence (www.aaai.org). All rights reserved. The problem is that future interest rates are volatile and that the value of c can be uncertain, too. The value of p is then uncertain as well. The question is: How to represent uncertain numerical values and how to compute them? Another major limitation of spreadsheets is that the relationships between cell values can only be expressed with functions evaluating output cell values from given input cell values. In the real world things are more complicated. For example, consider the following formula for computing the y-coordinate of a projectile trajectory as a function of the x-coordinate, firing angle a and initial velocity v. ) cos(a) /(v a 9.81 2 1 tan(a) x y 2 2 2 ⋅ ⋅ ⋅ + ⋅ = (1.2) Assume that the target is on a 120m high hill (y) at a distance of 3200m (x). The initial speed (v) of the projectile is between 1250 m/s and 1300m/s. The task is to find out what are the possible angles (a) between 0 and 90 degrees for hitting the target. The formula and the given data clearly provide the answer but it is not clear how to back solve a from the function. In a more general setting, the application problem may consist of a set of functions, equations, and inequalities, and the task is to solve any subset of variables involved, not only one variable. For example, what are the solutions to the equations below? 2 ) cos(x ) sin(x ) ln(x 3 3 ) -sin(x ) ln(x 2 ) cos(x ) ln(x ) cos(x ) sin(x